package com.nmgydy.admin.controller;

import com.alibaba.fastjson.JSON;
import com.nmgydy.beans.ListBean;
import com.nmgydy.beans.Message;
import com.nmgydy.beans.ResultBean;
import com.nmgydy.model.*;
import com.nmgydy.util.ConfigUtil;
import com.nmgydy.util.Functions;
import com.nmgydy.util.HibernateDao;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.*;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;
import org.springframework.web.multipart.MultipartHttpServletRequest;
import org.springframework.web.multipart.MultipartResolver;
import org.springframework.web.multipart.commons.CommonsMultipartResolver;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;


@Controller
@RequestMapping("/admin/history")
public class HistoryController {

	@Resource
	private HibernateDao hibernateDao;

	@RequestMapping(value = "list", method = RequestMethod.GET)
	public String list(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/admin/history/list";
	}

	@RequestMapping(value = "search", method = RequestMethod.GET)
	public String search(HttpServletRequest request, HttpServletResponse response, Model model) {
		return "/admin/history/search";
	}

	@RequestMapping(value = "listData", method = RequestMethod.POST)
	@ResponseBody
	public String listData(HttpServletRequest request, HttpServletResponse response, Model model) {
		String pageSize = request.getParameter("pageSize");
		String pageIndex = request.getParameter("pageIndex");
		String sortField = request.getParameter("sortField");
		String sortOrder = request.getParameter("sortOrder");

		String hql = "select a.id,a.caseType,a.taskName,a.totDays,a.curDays,a.userName,a.moveTime,a.status,c.name,c.number" +
				" from cn_move_history a,cn_court c where a.courtId=c.id ";
		Map<String, Object> params = new HashMap<>();

		String userName = request.getParameter("userName");
		if (!Functions.isEmpty(userName)) {
			hql += " and a.userName like :userName";
			params.put("userName",  "%" + userName + "%");
		}
		String taskName = request.getParameter("taskName");
		if (!Functions.isEmpty(taskName)) {
			hql += " and a.taskName like :taskName";
			params.put("taskName",  "%" + taskName + "%");
		}
		String caseType = request.getParameter("caseType");
		if (!Functions.isEmpty(caseType)) {
			hql += " and a.caseType=:caseType";
			params.put("caseType", caseType);
		}
		String moveTime = request.getParameter("moveTime");
		if (!Functions.isEmpty(moveTime)) {
			hql += " and a.moveTime like :moveTime";
			params.put("createTime", moveTime + "%");
		}
		String status = request.getParameter("status");
		if (!Functions.isEmpty(status)) {
			hql += " and a.status=status";
			params.put("status", status);
		}

		Boolean sorted = !(sortField == null || "".equals(sortField) || sortOrder == null || "".equals(sortOrder));
		if (sorted) {
			hql += " order by :sortField :sortOrder";
			params.put("sortField", sortField);
			params.put("sortOrder", sortOrder);
		}else{
			hql += " order by a.id desc";
		}

		Boolean paged = !(pageSize == null || "".equals(pageSize) || pageIndex == null || "".equals(pageIndex));
		if (paged) {
			int page = Integer.parseInt(pageSize);
			int start = page * Integer.parseInt(pageIndex);
			hql += " limit "+start+","+page;
		}
		Session session = null;
		try {
			System.out.println(hql);
			//开始数据库操作
			session = hibernateDao.getSessionFactory().openSession();
			SQLQuery query = session.createSQLQuery(hql);
			for (String key : params.keySet()) {
				Object value = params.get(key);
				query.setParameter(key, value);
			}
			List<?> list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();
			return JSON.toJSONString(new ListBean(list));
		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}

	@RequestMapping(value = "export", method = RequestMethod.POST)
	@ResponseBody
	public String export(HttpServletRequest request, HttpServletResponse response, Model model) {
		Session session = null;
		try {
			//开始数据库操作
			session = hibernateDao.getSessionFactory().openSession();
			//加载数据

			String hql = "select a.id,a.caseType,a.taskName,a.totDays,a.curDays,a.userName,a.moveTime,a.status,c.name,c.number" +
					" from cn_move_history a,cn_court c where a.courtId=c.id ";
			Map<String, Object> params = new HashMap<>();

			String userName = request.getParameter("userName");
			if (!Functions.isEmpty(userName)) {
				hql += " and a.userName like :userName";
				params.put("userName",  "%" + userName + "%");
			}
			String taskName = request.getParameter("taskName");
			if (!Functions.isEmpty(taskName)) {
				hql += " and a.taskName like :taskName";
				params.put("taskName",  "%" + taskName + "%");
			}
			String caseType = request.getParameter("caseType");
			if (!Functions.isEmpty(caseType)) {
				hql += " and a.caseType=:caseType";
				params.put("caseType", caseType);
			}
			String moveTime = request.getParameter("moveTime");
			if (!Functions.isEmpty(moveTime)) {
				hql += " and a.moveTime like :moveTime";
				params.put("createTime", moveTime + "%");
			}
			String status = request.getParameter("status");
			if (!Functions.isEmpty(status)) {
				hql += " and a.status=status";
				params.put("status", status);
			}
			SQLQuery query = session.createSQLQuery(hql);
			for (String key : params.keySet()) {
				Object value = params.get(key);
				query.setParameter(key, value);
			}
			List<?> list = query.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP).list();

			if (list.size() <= 0) {
				return JSON.toJSONString(ResultBean.failure("没有数据"));
			}
			//读取文件
			String tplFileName = "HistoryExport.xlsx";
			String path = Court.class.getResource("/").getPath();
			String filePath = path + "../tpl/" + tplFileName;
			File file = new File(filePath);
			XSSFWorkbook workbook = null;
			try {
				FileInputStream fileInputStream = new FileInputStream(file);
				workbook = new XSSFWorkbook(fileInputStream);
			} catch (Exception e) {
				e.printStackTrace();
			}
			XSSFSheet sheet = workbook.getSheet("sheet1");
			//写入文件
			XSSFCellStyle textCellStyle = workbook.createCellStyle();
			textCellStyle.setBorderTop(BorderStyle.THIN);
			textCellStyle.setBorderBottom(BorderStyle.THIN);
			textCellStyle.setBorderLeft(BorderStyle.THIN);
			textCellStyle.setBorderRight(BorderStyle.THIN);

			XSSFDataFormat numberFormart = workbook.createDataFormat();
			XSSFCellStyle numberCellStyle = workbook.createCellStyle();
			numberCellStyle.setAlignment(HorizontalAlignment.RIGHT);
			numberCellStyle.setDataFormat(numberFormart.getFormat("#,##0.00"));
			numberCellStyle.setBorderTop(BorderStyle.THIN);
			numberCellStyle.setBorderBottom(BorderStyle.THIN);
			numberCellStyle.setBorderLeft(BorderStyle.THIN);
			numberCellStyle.setBorderRight(BorderStyle.THIN);

			XSSFCell cell;
			for (int i = 0; i < list.size(); i++) {
				XSSFRow row = sheet.createRow(i + 1);
				Map<String,Object> rowMap = (Map<String, Object>) list.get(i);
				cell = row.createCell(0);
				cell.setCellStyle(textCellStyle);
				cell.setCellValue((String) rowMap.get("name"));
				cell = row.createCell(1);
				cell.setCellStyle(textCellStyle);
				cell.setCellValue((String) rowMap.get("number"));
				cell = row.createCell(2);
				cell.setCellStyle(textCellStyle);
				caseType = (String) rowMap.get("caseType");
				if(caseType.equals("1")){
					cell.setCellValue("民事案件");
				}else if(caseType.equals("2")){
					cell.setCellValue("刑事案件");
				}else if(caseType.equals("3")){
					cell.setCellValue("执行案件");
				}
				cell = row.createCell(3);
				cell.setCellStyle(textCellStyle);
				cell.setCellValue((String) rowMap.get("taskName"));
				cell = row.createCell(4);
				cell.setCellStyle(textCellStyle);
				cell.setCellValue((Integer) rowMap.get("totDays"));
				cell = row.createCell(5);
				cell.setCellStyle(textCellStyle);
				cell.setCellValue((Integer) rowMap.get("curDays"));
				cell = row.createCell(6);
				cell.setCellStyle(textCellStyle);
				cell.setCellValue((String) rowMap.get("userName"));
				cell = row.createCell(7);
				cell.setCellStyle(textCellStyle);
				cell.setCellValue((String) rowMap.get("moveTime"));
				cell = row.createCell(8);
				status = (String) rowMap.get("status");
				cell.setCellStyle(textCellStyle);
				if(status.equals("1")){
					cell.setCellValue("进行中");
				}else if(status.equals("2")){
					cell.setCellValue("已完成");
				}else if(status.equals("3")){
					cell.setCellValue("已逾期");
				}
			}
			//保存文件
			String webPath = request.getSession().getServletContext().getRealPath("/");
			String realFilePath = webPath + "upload";
			if (!(new File(realFilePath).exists())) {
				new File(realFilePath).mkdirs();
			}
			String fileName = Functions.getUUID() + ".xlsx";
			String saveFilePath = realFilePath + File.separator + fileName;
			ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
			try {
				workbook.write(byteArrayOutputStream);
				File outfile = new File(saveFilePath);
				FileOutputStream fileOutputStream = new FileOutputStream(outfile);
				fileOutputStream.write(byteArrayOutputStream.toByteArray());
				fileOutputStream.flush();
				fileOutputStream.close();
			} catch (Exception e) {
				e.printStackTrace();
				return JSON.toJSONString(ResultBean.failure("文件保存失败"));
			}
			//返回下载地址
			String rootPath = request.getContextPath();
			String uploadUrl = rootPath + "/upload/" + fileName;
			return JSON.toJSONString(ResultBean.success(uploadUrl));

		} catch (Exception e) {
			e.printStackTrace();
			return JSON.toJSONString(ResultBean.failure("查询失败"));
		} finally {
			if (session != null) {
				session.close();
			}
		}
	}
}
